import xlrd
import pandas as pd

# 定义常量
file_path='C:\\Users\\me\\Downloads\\生产环境表数据字典.xlsx'
target_table_path='C:\\Users\\me\Downloads\\指标梳理-YDM.xlsx'
out_path='C:\\Users\\me\\Downloads\\out1.sql'
df =pd.read_excel(file_path,sheet_name='Sheet1',usecols=['表名称','库名','字段名称','字段备注','字段类型','字段顺序'],keep_default_na=False)
df_table=pd.read_excel(target_table_path,sheet_name='Sheet3',usecols=['新增名称','新增表名'],keep_default_na=False)
 
    
# 非分区表
sql_pattern1='''
drop table if exists {}.{};
create table {}.{} (
{}
)
WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE)
DISTRIBUTE BY HASH ({})
;
COMMENT ON table {}.{} IS '{}';
{}
'''
# 分区表
sql_pattern2='''
--drop table if exists {}.{};
create table if not exists {}.{} (
{},
partitiondate date
)
WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE)
DISTRIBUTE BY HASH ({})
PARTITION BY RANGE (partitiondate )(   
  PARTITION dt START(trunc(NOW())) END(trunc(NOW() + INTERVAL '1' MONTH)) EVERY(INTERVAL '1' DAY)
);
COMMENT ON table {}.{} IS '{}';
{}
'''


#库名、表名、字段、字段备注
sql_comment='''comment on column  {}.{}.{} is '{}';\n'''
#
sql_col='''{} {},\n'''

d={}
for line in df_table.values:
    if line is not None:
        d[line[0]]=line[0]



with open(out_path,mode='w',encoding='utf-8') as w:
    for key in d.keys():
        #定义变量
        table_name=str()
        database_name=str()
        sql=str()
        sql_comments=str()
        sql_cols=str()
        first_col=str()
        for line in df.values:
            if str(line[0]).lower().strip()==str(key).lower().strip():
                # print('表名称:{},库名:{},字段名称:{},字段备注:{},字段类型:{},字段顺序:{}'.format(line[0],line[1],line[2],line[3],line[4],line[5]))
                if line[0] is not None:
                    table_name=line[0]
                    database_name=line[1]
                    sql_comments+=sql_comment.format(database_name,table_name,line[2],line[3])
                    sql_cols+=sql_col.format(line[2],line[4])
                    if line[5]==0 and str(line[2]).lower().find('date')<0:
                        first_col=line[2]
                    elif line[5]==1 and len(first_col)==0:
                        first_col=line[2]
        index=sql_cols.rfind(',')
        sql_cols=sql_cols[:index]
        if str(key).lower().find('dws')>=0:
            w.write(sql_pattern2.format(database_name,table_name,database_name,table_name,sql_cols,first_col,database_name,table_name,d[key],sql_comments))
        else:
            w.write(sql_pattern1.format(database_name,table_name,database_name,table_name,sql_cols,first_col,database_name,table_name,d[key],sql_comments))
        